﻿/*
    Copyright 2013 Bob Moss (bobbyjmoss@gmail.com)

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace OpenRAMS.Model
{
    public class WorkTicket
    {
        //exposed attributes of work ticket
        public int workTicketID { get; set; }
        public String workTicketCreator { get; set; }
        public String workTicketLocation { get; set; }
        public String workTicketAllocatedTo { get; set; }
        public String workTicketTitle { get; set; }
        public String workTicketDesc { get; set; }
        public Boolean workTicketApprovalStatus { get; set; }
        public Boolean workTicketActive { get; set; }
        public DateTime createdTimeStamp { get; set; }

        //Will eventually list relevant log entries
        private WorkTicketLogs wtLogs;

        public WorkTicketLogs getWorkTicketLogs()
        {
            return wtLogs;
        }

        //internal class variables/arrays
        protected String[] attribNames;
        protected int numAttribs;
        private bool NewOrOld;
        protected String tableName;

        //database-related variables
        private SqlConnection dbConnection;
        private SqlDataReader dbReader;
        private SqlCommand dbCommand;
        private String dbInString;

        public WorkTicket()
        {
            initHardCodedVals();
            NewOrOld = true;    //if true, is new record

            workTicketID=-1;
            workTicketCreator="";
            workTicketLocation="";
            workTicketAllocatedTo="";
            workTicketTitle="";
            workTicketDesc="";
            workTicketApprovalStatus=false;
            workTicketActive=true;
            createdTimeStamp = Convert.ToDateTime(System.DateTime.Now.ToString());

            //initialise lists
            wtLogs = new WorkTicketLogs();

            //reads in db connection string
            try { dbInString = ConfigurationManager.ConnectionStrings["default"].ConnectionString; }
            catch (Exception e) { throw e; }  //this will cause error conditions later
        }

        public int Load(int wkTicketID)
        {
            //verify parameter is sensible
            if (wkTicketID < 0) { return -1; }

            //reinitialise lists
            wtLogs = new WorkTicketLogs();

            //check for any log entries
            int logCheck = wtLogs.Populate(wkTicketID);
            //not worried if no logs yet. They are added over the course of a job

            //Build query string
            //Build query string
            String queryFull = "SELECT * FROM " + tableName + " ", queryCondition = "WHERE " + attribNames[0] + "=" + wkTicketID;
            int rCheck = 0; //used for error-checking later
            String[] outputVars = new String[numAttribs + 1];

            NewOrOld = false;   //importing existing record

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //build query string
            queryFull += queryCondition;
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;


            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //initialise output array
            for (int i = 0; i < outputVars.Length; i++)
            {
                outputVars[i] = "";
            }

            //Import values from query into string array
            while (dbReader.Read())
            {
                if (rCheck > 0)
                {
                    return -1;  //if more than one result was returned, run hard & run fast!
                }
                else
                {
                    int i = 0;  //iterator for output string
                    foreach (String att in attribNames)
                    {
                        try { outputVars[i] = dbReader[att].ToString(); }
                        catch { return -1; }
                        i++;
                    }
                }
                rCheck++;   //tracks number of results
            }

            //Verify that at least one result was returned
            if (rCheck < 1) { return -1; };

            //Import values from Db into class
            try { workTicketID = Convert.ToInt32(outputVars[0]); }
            catch { return -1; }

            workTicketCreator = outputVars[1];
            workTicketLocation = outputVars[2];
            workTicketAllocatedTo = outputVars[3];
            workTicketTitle = outputVars[4];
            workTicketDesc = outputVars[5];

            try { workTicketApprovalStatus = Convert.ToBoolean(outputVars[6]); }
            catch { return -1; }
            try { workTicketActive = Convert.ToBoolean(outputVars[7]); }
            catch { return -1; }
            try { createdTimeStamp = DateTime.Parse(outputVars[8]); }
            catch { return -1; }

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

           NewOrOld = false; //fail-safe
           return 0;
        }

        public int Save()
        {
            //Strings to handle query as it's built
            String queryFull = "", queryCondition = "";

            //is this a new record? If so, we need an INSERT statement. Else, an UPDATE
            if (NewOrOld == true)
            {
                queryFull = "INSERT INTO " + tableName + " (";
                //the numerical ID increments for us, so we just need to state which fields we're inserting into
                int i = 0;
                foreach (String att in attribNames)
                {
                    if (i > 0 && i<attribNames.Count()-1)
                    {
                        queryFull += att + ", ";
                    }
                    if (i == attribNames.Count()-1)
                    {
                        queryFull += att + ") ";
                    }
                    i++;
                }
                queryFull += "VALUES (";
                queryFull += "'" + workTicketCreator + "', ";
                queryFull += "'" + workTicketLocation + "', ";
                queryFull += "'" + workTicketAllocatedTo + "', ";
                queryFull += "'" + workTicketTitle + "', ";
                queryFull += "'" + workTicketDesc + "', ";
                queryFull += "'" + Convert.ToString(workTicketApprovalStatus) + "', ";
                queryFull += "'" + Convert.ToString(workTicketActive) + "', ";

                queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')";
            }
            else
            {
                //Verify record exists
                WorkTicket test = new WorkTicket();
                int aTest = test.Load(workTicketID);
                if (aTest < 0) { return -1; }

                //build the UPDATE query (clientName is skipped because it's a primary key)
                queryFull = "UPDATE " + tableName + " SET ";
                queryCondition = "WHERE " + attribNames[0] + "=" + workTicketID;

                queryFull += attribNames[1] + "='" + workTicketCreator + "', ";
                queryFull += attribNames[2] + "='" + workTicketLocation + "', ";
                queryFull += attribNames[3] + "='" + workTicketAllocatedTo + "', ";
                queryFull += attribNames[4] + "='" + workTicketTitle + "', ";
                queryFull += attribNames[5] + "='" + workTicketDesc + "', ";
                queryFull += attribNames[6] + "='" + Convert.ToString(workTicketApprovalStatus) + "', ";
                queryFull += attribNames[7] + "='" + Convert.ToString(workTicketActive) + "', ";

                queryFull += attribNames[8] + "='" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "' ";
                queryFull += queryCondition;
            }

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //create command that will run query
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

            NewOrOld = false;   //fail-safe

            return 0;
        }

        public int Delete()
        {
            //Work tickets should never be destroyed, only made 'inactive'
            workTicketActive = false;
            int makeInactive = Save();
            if (makeInactive < 0) { return -1; }
            return 0;
        }
        
        protected void initHardCodedVals()
        {
            //set hard-coded values
            numAttribs = 8;
            tableName = "tblWorkTickets";
            attribNames = new String[numAttribs + 1];
            attribNames[0] = "intWorkTicketID"; //primary key value
            attribNames[1] = "txtUserName";
            attribNames[2] = "txtRoom";
            attribNames[3] = "txtAllocatedTo";
            attribNames[4] = "txtTitle";
            attribNames[5] = "txtDescription";
            attribNames[6] = "bitApproved";
            attribNames[7] = "bitActive";
            attribNames[8] = "dtTimeStamp";
        }
    }
}